package com.database.management.base;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import javax.sql.DataSource;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.database.management.db.DbPoolConnection;
import com.database.management.domain.DbConnection;

/**
 * @ClassName: DBUtilsCRUDTest
 * @Description:使用dbutils框架的QueryRunner类完成CRUD,以及批处理
 * @author: 孤傲苍狼
 * @date: 2014-10-5 下午4:56:44
 *
 */
public abstract class BaseDao {

	public static final Map<Integer, DataSource> cacheConnection = new ConcurrentHashMap<>();
	
	private Integer key;
	
	public BaseDao(Integer key) {
		this.key = key;
	}
	
	public static void addConnection(DbConnection db){
		try {
			Map<String, String> properties = BeanUtils.describe(db);
			BaseDao.cacheConnection.put(db.getId(), DruidDataSourceFactory.createDataSource(properties));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public DataSource getDataSource() {
		return cacheConnection.get(key);
	}

	public int update(String sql) throws SQLException {
		Connection conn = getDataSource().getConnection();
		PreparedStatement pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		pst.executeUpdate();
		ResultSet rs = pst.getGeneratedKeys();
		rs.next();
		return rs.getInt(1);
	}

	public List<Map<String, Object>> find(String sql) throws SQLException {
		QueryRunner run = new QueryRunner(getDataSource());
		return run.query(sql, new MapListHandler());
	}

	public boolean createConnection(Integer id) throws Exception {
		boolean falg = false;
		String sql = "select * from t_db_connection";
		if (id != null) {
			sql += " where id = " + id;
		}
		List<Map<String, Object>> resultList = find(sql);
		Map<String, Object> properties = new HashMap();
		for (String key : resultList.get(0).keySet()) {
			properties.put(key.toLowerCase(), resultList.get(0).get(key));
		}
		if (resultList.size() > 0) {
			cacheConnection.put(id, DruidDataSourceFactory.createDataSource(properties));
			falg = true;
		}
		return falg;
	}

	public Map<Integer, DataSource> getCacheConnection() {
		return cacheConnection;
	}

	public List getAllTableName() throws SQLException {
		List tables = new ArrayList();
		DatabaseMetaData dbMetaData = getDataSource().getConnection().getMetaData();
		// 可为:"TABLE", "VIEW", "SYSTEM TABLE",
		// "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"
		String[] types = { "TABLE" };
		ResultSet tabs = dbMetaData.getTables(null, dbMetaData.getUserName(), null, types);
		/*
		 * 记录集的结构如下: TABLE_CAT String => table catalog (may be null) TABLE_SCHEM
		 * String => table schema (may be null) TABLE_NAME String => table name
		 * TABLE_TYPE String => table type. REMARKS String => explanatory
		 * comment on the table TYPE_CAT String => the types catalog (may be
		 * null) TYPE_SCHEM String => the types schema (may be null) TYPE_NAME
		 * String => type name (may be null) SELF_REFERENCING_COL_NAME String =>
		 * name of the designated "identifier" column of a typed table (may be
		 * null) REF_GENERATION String => specifies how values in
		 * SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER",
		 * "DERIVED". (may be null)
		 */
		while (tabs.next()) {
			Map map = new HashMap();
			map.put("text", tabs.getObject("TABLE_NAME"));
			map.put("id", tabs.getObject("TABLE_NAME"));
			map.put("state","closed");
			List columns = new ArrayList();
			ResultSet rs = dbMetaData.getColumns("", dbMetaData.getUserName(), tabs.getObject("TABLE_NAME").toString(),
					null);
			while (rs.next()) {
				Map columnm = new HashMap();
				columnm.put("text", rs.getString("COLUMN_NAME"));
				columnm.put("id", rs.getString("COLUMN_NAME"));
				columns.add(columnm);
			}
			map.put("children", columns);
			// 只要表名这一列
			tables.add(map);
		}
		return tables;

	}

}